/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package servermba.mdb;

import com.google.gson.Gson;
import com.google.gson.GsonBuilder;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import servermba.object.CreditCard;
import servermba.object.ResultReponse;
import servermba.object.SavingAccount;
import servermba.object.Users;

/**
 *
 * @author lekhanhduy
 */
public class ApiManeger {

    public ApiManeger() {
    }

    /**
     * ham login
     *
     * @param accountNumber
     * @param pass
     * @return
     */
    public boolean login(String accountNumber, String pass) {
        Connection connection = ConnectDB.openConnect();
        CallableStatement callableStatement = null;
        try {

            String getDBUSERByUserIdSql = "{call login(?,?,?)}";
            callableStatement = connection.prepareCall(getDBUSERByUserIdSql);

            callableStatement.setString("accountCode", accountNumber);
            callableStatement.setString("accountPass", pass);
            callableStatement.registerOutParameter("result", java.sql.Types.INTEGER);

            callableStatement.executeQuery();

            int result = callableStatement.getInt(3);
            if (result == 1) {
                return true;
            } else {
                return false;
            }

        } catch (SQLException e) {
        } finally {
            try {
                ConnectDB.closeConnect(connection);
                if (callableStatement != null) {
                    callableStatement.close();
                }
            } catch (SQLException e) {
            }
        }
        return false;
    }

    public boolean loginSavingAccount(String accountNumber) {
        Connection connection = ConnectDB.openConnect();
        CallableStatement callableStatement = null;
        try {
            System.out.println(accountNumber);
            String getDBUSERByUserIdSql = "{call loginSavingAccount(?,?)}";
            callableStatement = connection.prepareCall(getDBUSERByUserIdSql);

            callableStatement.setString("accountNumber", accountNumber);
            callableStatement.registerOutParameter("result", java.sql.Types.INTEGER);

            callableStatement.executeQuery();

            int result = callableStatement.getInt(2);
            if (result == 1) {
                return true;
            } else {
                return false;
            }

        } catch (SQLException e) {
        } finally {
            try {
                ConnectDB.closeConnect(connection);
                if (callableStatement != null) {
                    callableStatement.close();
                }
            } catch (SQLException e) {
            }
        }
        return false;
    }

    public CreditCard getCreditCardOfUser(String accountNumber) {
        CreditCard creditCard = new CreditCard();
        Connection connection = ConnectDB.openConnect();
        CallableStatement callableStatement = null;

        try {
            String getDBUSERByUserIdSql = "{call getInfoUserCreditCard(?)}";
            callableStatement = connection.prepareCall(getDBUSERByUserIdSql);

            callableStatement.setString("accountCode", accountNumber);
            ResultSet result = callableStatement.executeQuery();
            if (result != null && result.first()) {
                creditCard.setAccountCode(result.getString(CreditCard.AccountCode));
                creditCard.setAccountPass(result.getString(CreditCard.AccountPass));
                creditCard.setAddress(result.getString(CreditCard.Address));
                creditCard.setBalance(result.getDouble(CreditCard.Balance));
                creditCard.setIdentityCardNumber(result.getString(CreditCard.IdentityCardNumber));
                creditCard.setFirstName(result.getString(CreditCard.FirstName));
                creditCard.setLastName(result.getString(CreditCard.LastName));
                creditCard.setDateOfBirth(result.getString(CreditCard.DateOfBirth));
                creditCard.setPhone(result.getString(CreditCard.Phone));
                creditCard.setEmail(result.getString(CreditCard.Email));
                creditCard.setStaffBank(result.getBoolean(CreditCard.StaffBank));
            }
        } catch (SQLException e) {
        } finally {
            try {
                ConnectDB.closeConnect(connection);
                if (callableStatement != null) {
                    callableStatement.close();
                }
            } catch (SQLException e) {
            }
        }

        return creditCard;
    }

    public SavingAccount getInfoSavingAccount(String accountNumber) {
        SavingAccount savingAccount = new SavingAccount();
        Connection connection = ConnectDB.openConnect();
        CallableStatement callableStatement = null;

        try {
            String getDBUSERByUserIdSql = "{call getInfoUserSavingAccount(?)}";
            callableStatement = connection.prepareCall(getDBUSERByUserIdSql);

            callableStatement.setString("accountNumber", accountNumber);
            ResultSet result = callableStatement.executeQuery();
            if (result != null && result.first()) {
                savingAccount.setAccountNumber(result.getString(SavingAccount.AccountNumber));
                savingAccount.setBalance(result.getDouble(SavingAccount.Balance));
                savingAccount.setPeriodTimeout(result.getInt(SavingAccount.PeriodTimeout));
                savingAccount.setInterest(result.getDouble(SavingAccount.Interest));
                savingAccount.setCurrency(result.getString(SavingAccount.Currency));
                SimpleDateFormat dt = new SimpleDateFormat("dd-MM-yyyy");
                savingAccount.setCreateDate(dt.format(result.getDate(SavingAccount.CreateDate)).toString());
                savingAccount.setDateInterestNewest(dt.format(result.getDate(SavingAccount.DateInterestNewest)).toString());
                savingAccount.setIdentityCardNumber(result.getString(Users.IdentityCardNumber));
                savingAccount.setFirstName(result.getString(Users.FirstName));
                savingAccount.setAddress(result.getString(Users.Address));
                savingAccount.setLastName(result.getString(Users.LastName));
                savingAccount.setDateOfBirth(result.getString(Users.DateOfBirth));
                savingAccount.setPhone(result.getString(Users.Phone));
                savingAccount.setEmail(result.getString(Users.Email));
                savingAccount.setStaffBank(result.getBoolean(Users.StaffBank));
            }
        } catch (SQLException e) {
        } finally {
            try {
                ConnectDB.closeConnect(connection);
                if (callableStatement != null) {
                    callableStatement.close();
                }
            } catch (SQLException e) {
            }
        }
        return savingAccount;
    }

    public String sentWithDrawMoney(String accountCode, String method, double money) {
        String result = "";
        double balance = getBalanceOfCrediCard(accountCode);
        if (method.equals("sent")) {
            Connection connection = ConnectDB.openConnect();
            CallableStatement callableStatement = null;
            try {
                String getDBUSERByUserIdSql = "{call credit_card_sent_withdraw(?,?,?,?)}";
                callableStatement = connection.prepareCall(getDBUSERByUserIdSql);

                callableStatement.setString("accountCode", accountCode);
                callableStatement.setString("method", method);
                callableStatement.setDouble("money", money);
                callableStatement.setDouble("balance", balance + money);
                callableStatement.executeQuery();
            } catch (SQLException e) {
            } finally {
                try {
                    ConnectDB.closeConnect(connection);
                    if (callableStatement != null) {
                        callableStatement.close();
                    }
                } catch (SQLException e) {
                }
                ResultReponse reponse = new ResultReponse();
                reponse.setSuccess(true);
                reponse.setNote("Gửi tiền thành công!");
                Gson gson = new GsonBuilder().serializeNulls().create();
                result = gson.toJson(reponse);
            }
        } else if (method.equals("withdraw")) {
            //neu so tien muon rut ma vuot qua so tien co trong tai khoan
            if (money > balance - 50000) {
                ResultReponse reponse = new ResultReponse();
                reponse.setSuccess(false);
                reponse.setNote("Số tiền muốn rút vượt quá số dư trong tài khoản!");
                Gson gson = new GsonBuilder().serializeNulls().create();
                result = gson.toJson(reponse);
            } else if (money > 2000000) {
                ResultReponse reponse = new ResultReponse();
                reponse.setSuccess(false);
                reponse.setNote("Số tiền rút 1 lần không được vượt quá 2.000.000 VND!");
                Gson gson = new GsonBuilder().serializeNulls().create();
                result = gson.toJson(reponse);
            } else {
                java.util.Date date = new java.util.Date();
                SimpleDateFormat dt = new SimpleDateFormat("yyyy-MM-dd");
                Date dateTemp = Date.valueOf(dt.format(date));
                double sum = getBalanceTransactionByDate(accountCode, dateTemp);
                if (sum == 10000000) {
                    ResultReponse reponse = new ResultReponse();
                    reponse.setSuccess(false);
                    reponse.setNote("Bạn đã rút hết tối đa 10.000.000 VND trong ngày");
                    Gson gson = new GsonBuilder().serializeNulls().create();
                    result = gson.toJson(reponse);
                } else if (sum + money > 10000000) {
                    ResultReponse reponse = new ResultReponse();
                    reponse.setSuccess(false);
                    reponse.setNote("Trong 1 ngày không được rút quá 10.000.000 VND \n Bạn chỉ được rút " + (10000000 - sum));
                    Gson gson = new GsonBuilder().serializeNulls().create();
                    result = gson.toJson(reponse);
                } else {
                    Connection connection = ConnectDB.openConnect();
                    CallableStatement callableStatement = null;
                    try {

                        String getDBUSERByUserIdSql = "{call credit_card_sent_withdraw(?,?,?,?)}";
                        callableStatement = connection.prepareCall(getDBUSERByUserIdSql);

                        callableStatement.setString("accountCode", accountCode);
                        callableStatement.setString("method", method);
                        callableStatement.setDouble("money", money);
                        callableStatement.setDouble("balance", balance - money);
                        callableStatement.executeQuery();
                    } catch (SQLException e) {
                    } finally {
                        try {
                            ConnectDB.closeConnect(connection);
                            if (callableStatement != null) {
                                callableStatement.close();
                            }
                        } catch (SQLException e) {
                        }
                        ResultReponse reponse = new ResultReponse();
                        reponse.setSuccess(true);
                        reponse.setNote("Rút tiền thành công !");
                        Gson gson = new GsonBuilder().serializeNulls().create();
                        result = gson.toJson(reponse);
                    }
                }
            }
        }
        return result;
    }

    public double getBalanceOfCrediCard(String accountCode) {
        Connection connection = ConnectDB.openConnect();
        CallableStatement callableStatement = null;
        double balance = 0;
        try {

            String getDBUSERByUserIdSql = "{call getBalanceCreditCard(?,?)}";
            callableStatement = connection.prepareCall(getDBUSERByUserIdSql);

            callableStatement.setString("accountCode", accountCode);
            callableStatement.registerOutParameter("balance", java.sql.Types.INTEGER);

            callableStatement.executeQuery();

            balance = callableStatement.getDouble(2);

        } catch (SQLException e) {
        } finally {
            try {
                ConnectDB.closeConnect(connection);
                if (callableStatement != null) {
                    callableStatement.close();
                }
            } catch (SQLException e) {
            }
        }
        return balance;
    }

    public double getBalanceOfSavingAccount(String accountNumber) {
        Connection connection = ConnectDB.openConnect();
        CallableStatement callableStatement = null;
        double balance = 0;
        try {
            String getDBUSERByUserIdSql = "{call getBalanceSavingAccount(?,?)}";
            callableStatement = connection.prepareCall(getDBUSERByUserIdSql);

            callableStatement.setString("AccountNumber", accountNumber);
            callableStatement.registerOutParameter("balance", java.sql.Types.DOUBLE);
            callableStatement.executeQuery();
            balance = callableStatement.getDouble(2);
        } catch (SQLException e) {
        } finally {
            try {
                ConnectDB.closeConnect(connection);
                if (callableStatement != null) {
                    callableStatement.close();
                }
            } catch (SQLException e) {
            }
        }
        return balance;
    }

    public boolean checkTransfersCreditCard(String user) {
        Connection connection = ConnectDB.openConnect();
        CallableStatement callableStatement = null;
        try {

            String getDBUSERByUserIdSql = "{call check_transfers(?,?)}";
            callableStatement = connection.prepareCall(getDBUSERByUserIdSql);

            callableStatement.setString("user", user);
            callableStatement.registerOutParameter("result", java.sql.Types.INTEGER);

            callableStatement.executeQuery();

            int result = callableStatement.getInt(2);
            if (result == 1) {
                return true;
            } else {
                return false;
            }

        } catch (SQLException e) {
        } finally {
            try {
                ConnectDB.closeConnect(connection);
                if (callableStatement != null) {
                    callableStatement.close();
                }
            } catch (SQLException e) {
            }
        }
        return false;
    }

    public ResultReponse transfers_creditcard(String userSent, String userRev, double money) {
        double balance = getBalanceOfCrediCard(userSent);
        //neu so tien muon rut ma vuot qua so tien co trong tai khoan
        if (money > balance - 50000) {
            ResultReponse reponse = new ResultReponse();
            reponse.setSuccess(false);
            reponse.setNote("Số tiền muốn chuyển quá số dư trong tài khoản!");
            return reponse;
        } else if (money > 2000000) {
            ResultReponse reponse = new ResultReponse();
            reponse.setSuccess(false);
            reponse.setNote("Số tiền chuyển 1 lần không được vượt quá 2.000.000 VND!");
            return reponse;
        } else {
            CreditCard sent = getCreditCardOfUser(userSent);
            CreditCard recv = getCreditCardOfUser(userRev);
            transfer(sent.getAccountCode(), recv.getIdentityCardNumber(), "transfer", sent.getBalance() - money, money);
            transfer(recv.getAccountCode(), sent.getIdentityCardNumber(), "revmoney", recv.getBalance() + money, money);
            ResultReponse reponse = new ResultReponse();
            reponse.setSuccess(true);
            reponse.setNote("Chuyển tiền thành công từ " + sent.getFirstName() + " " + sent.getLastName() + " đến "
                    + recv.getFirstName() + " " + recv.getLastName());
            return reponse;
        }
    }

    public void transfer(String userSent, String userRev, String method, double balance, double money) {
        Connection connection = ConnectDB.openConnect();
        CallableStatement callableStatement = null;
        try {

            String getDBUSERByUserIdSql = "{call transferCreditCard(?,?,?,?,?)}";
            callableStatement = connection.prepareCall(getDBUSERByUserIdSql);

            callableStatement.setString("accountCode", userSent);
            callableStatement.setString("userRev", userRev);
            callableStatement.setString("method", method);
            callableStatement.setDouble("money", money);
            callableStatement.setDouble("balance", balance);
            callableStatement.executeQuery();
        } catch (SQLException e) {
        } finally {
            try {
                ConnectDB.closeConnect(connection);
                if (callableStatement != null) {
                    callableStatement.close();
                }
            } catch (SQLException e) {
            }
        }
    }

    public String getAllHistory(String accountCode, Date fromDate, Date toDate) {
        Connection connection = ConnectDB.openConnect();
        CallableStatement callableStatement = null;

        try {
            String getDBUSERByUserIdSql = "{call getAllHistory(?,?,?)}";
            callableStatement = connection.prepareCall(getDBUSERByUserIdSql);

            callableStatement.setString("accountCode", accountCode);
            callableStatement.setDate("fromDate", fromDate);
            callableStatement.setDate("toDate", toDate);
            ResultSet result = callableStatement.executeQuery();
            List<Object[]> temp = new ArrayList<Object[]>();
            if (result != null) {
                while (result.next()) {
                    String method = result.getString("TypeTransaction");
                    switch (method) {
                        case "sent":
                            method = "Gửi tiền vào tài khoản";
                            break;
                        case "transfer":
                            method = "Chuyển tiền";
                            break;
                        case "revmoney":
                            method = "Nhận tiền";
                            break;
                        case "withdraw":
                            method = "Rút tiền từ tài khoản";
                            break;
                    }
                    String fistName = result.getString("FirstName");
                    if (fistName == null) {
                        fistName = "";
                    }
                    String lastName = result.getString("LastName");
                    if (lastName == null) {
                        lastName = "";
                    }
                    String name = fistName + " " + lastName;
                    SimpleDateFormat dt = new SimpleDateFormat("dd-MM-yyyy");
                    String Date = dt.format(result.getDate("CreateDate")).toString();
                    String Time = result.getTime("CreateTime").toString();
                    double money = result.getDouble("Balance");
                    Object[] data = {method, name, Date, Time, String.valueOf(money)};
                    temp.add(data);
                }
                Gson gson = new GsonBuilder().serializeNulls().create();
                String resultTepm = gson.toJson(temp);
                return resultTepm;
            }
        } catch (SQLException e) {
        } finally {
            try {
                ConnectDB.closeConnect(connection);
                if (callableStatement != null) {
                    callableStatement.close();
                }
            } catch (SQLException e) {
            }
        }
        return null;
    }

    public String getAllHistorySavingAccount(String accountNumber, Date fromDate, Date toDate) {
        Connection connection = ConnectDB.openConnect();
        CallableStatement callableStatement = null;

        try {
            String getDBUSERByUserIdSql = "{call getAllHistorySavingAccount(?,?,?)}";
            callableStatement = connection.prepareCall(getDBUSERByUserIdSql);

            callableStatement.setString("accountNumber", accountNumber);
            callableStatement.setDate("fromDate", fromDate);
            callableStatement.setDate("toDate", toDate);
            ResultSet result = callableStatement.executeQuery();
            List<Object[]> temp = new ArrayList<Object[]>();
            if (result != null) {
                while (result.next()) {
                    String method = result.getString("TypeTransaction");
                    SimpleDateFormat dt = new SimpleDateFormat("dd-MM-yyyy");
                    String Date = dt.format(result.getDate("CreateDate")).toString();
                    String Time = result.getTime("CreateTime").toString();
                    double money = result.getDouble("BalanceTransaction");
                    Object[] data = {method, Date, Time, String.valueOf(money)};
                    temp.add(data);
                }
                Gson gson = new GsonBuilder().serializeNulls().create();
                String resultTepm = gson.toJson(temp);
                return resultTepm;
            }
        } catch (SQLException e) {
        } finally {
            try {
                ConnectDB.closeConnect(connection);
                if (callableStatement != null) {
                    callableStatement.close();
                }
            } catch (SQLException e) {
            }
        }
        return null;
    }

    public ResultReponse changePassCreditCard(String accountCode, String pass) {
        Connection connection = ConnectDB.openConnect();
        CallableStatement callableStatement = null;
        try {

            String getDBUSERByUserIdSql = "{call changePassCreditCard(?,?)}";
            callableStatement = connection.prepareCall(getDBUSERByUserIdSql);

            callableStatement.setString("account", accountCode);
            callableStatement.setString("pass", pass);

            callableStatement.executeQuery();
            ResultReponse reponse = new ResultReponse();
            reponse.setSuccess(true);
            reponse.setNote("Đổi pass thành công!");
            return reponse;
        } catch (SQLException e) {
            ResultReponse reponse = new ResultReponse();
            reponse.setSuccess(true);
            reponse.setNote("Đổi pass không thành công!");
            return reponse;
        } finally {
            try {
                ConnectDB.closeConnect(connection);
                if (callableStatement != null) {
                    callableStatement.close();
                }
            } catch (SQLException e) {
            }
        }
    }

    public double getBalanceTransactionByDate(String accountCode, Date date) {
        Connection connection = ConnectDB.openConnect();
        CallableStatement callableStatement = null;
        double balance = 0;
        try {

            String getDBUSERByUserIdSql = "{call sumTransactionWithDraw(?,?,?)}";
            callableStatement = connection.prepareCall(getDBUSERByUserIdSql);
            callableStatement.setString("accountCode", accountCode);
            callableStatement.setDate("dateTrans", date);
            callableStatement.registerOutParameter("sumTrans", java.sql.Types.DOUBLE);

            callableStatement.executeQuery();

            balance = callableStatement.getDouble(3);
        } catch (SQLException e) {
        } finally {
            try {
                ConnectDB.closeConnect(connection);
                if (callableStatement != null) {
                    callableStatement.close();
                }
            } catch (SQLException e) {
            }
        }
        return balance;
    }

    public String getAllUserCreditCard() {
        Connection connection = ConnectDB.openConnect();
        CallableStatement callableStatement = null;

        try {
            String getDBUSERByUserIdSql = "{call getAllUserCreditCard()}";
            callableStatement = connection.prepareCall(getDBUSERByUserIdSql);

            ResultSet result = callableStatement.executeQuery();
            List<Object[]> temp = new ArrayList<Object[]>();
            if (result != null) {
                while (result.next()) {
                    String accountCode = result.getString("AccountCode").toString();
                    String fistName = result.getString("FirstName").toString();
                    if (fistName == null) {
                        fistName = "";
                    }
                    String lastName = result.getString("LastName").toString();
                    if (lastName == null) {
                        lastName = "";
                    }
                    String name = fistName + " " + lastName;
                    SimpleDateFormat dt = new SimpleDateFormat("dd-MM-yyyy");
                    String Date = dt.format(result.getDate("DateOfBirth")).toString();
                    double money = result.getDouble("Balance");
                    String Phone = result.getString("Phone").toString();
                    String Email = result.getString("Email").toString();
                    String Address = result.getString("Address").toString();
                    Object[] data = {accountCode, name, Date, String.valueOf(money), Phone, Email, Address};
                    temp.add(data);
                }
                Gson gson = new GsonBuilder().serializeNulls().create();
                String resultTepm = gson.toJson(temp);
                return resultTepm;
            }
        } catch (SQLException e) {
        } finally {
            try {
                ConnectDB.closeConnect(connection);
                if (callableStatement != null) {
                    callableStatement.close();
                }
            } catch (SQLException e) {
            }
        }
        return null;
    }

    public ResultReponse withDrawMoneySavingAccount(String accountNumber, double money) {
        ResultReponse reponse = null;
        double balance = getBalanceOfSavingAccount(accountNumber);
        if (balance > money) {
            Connection connection = ConnectDB.openConnect();
            CallableStatement callableStatement = null;
            try {

                String getDBUSERByUserIdSql = "{call withdrawSavingAccount(?,?,?)}";
                callableStatement = connection.prepareCall(getDBUSERByUserIdSql);
                callableStatement.setString("accountNumber", accountNumber);
                callableStatement.setDouble("money", money);
                callableStatement.setDouble("balance", balance - money);
                callableStatement.executeQuery();
            } catch (SQLException e) {
            } finally {
                try {
                    ConnectDB.closeConnect(connection);
                    if (callableStatement != null) {
                        callableStatement.close();
                    }
                } catch (SQLException e) {
                }
            }
            reponse = new ResultReponse();
            reponse.setSuccess(true);
            reponse.setNote("Rút tiền thành công");
        } else {
            reponse = new ResultReponse();
            reponse.setSuccess(false);
            reponse.setNote("Số tiền muốn rút vượt quá số dư trong tài khoản!");
        }
        return reponse;
    }

    public ResultReponse caculInterest(String accountNumber, Date date) {
        ResultReponse reponse = new ResultReponse();
        SavingAccount account = getInfoSavingAccount(accountNumber);
        String[] dateArray = account.getDateInterestNewest().split("-");
        int day = Integer.parseInt(dateArray[0]);
        int mount = Integer.parseInt(dateArray[1]);
        int year = Integer.parseInt(dateArray[2]);

        SimpleDateFormat dt = new SimpleDateFormat("dd-MM-yyyy");
        String dateCal = dt.format(date);
        String[] dateArrayCal = dateCal.split("-");
        int dayCal = Integer.parseInt(dateArrayCal[0]);
        int mountCal = Integer.parseInt(dateArrayCal[1]);
        int yearCal = Integer.parseInt(dateArrayCal[2]);

        int periodTimeout = account.getPeriodTimeout();
        if (yearCal >= year && ((yearCal - year) * 12 + mountCal - mount >= periodTimeout)) {
            if (dayCal >= day) {
                double temp = account.getBalance() * account.getInterest() / 100;
                Connection connection = ConnectDB.openConnect();
                CallableStatement callableStatement = null;
                try {

                    String getDBUSERByUserIdSql = "{call interestSavingAccount(?,?,?)}";
                    callableStatement = connection.prepareCall(getDBUSERByUserIdSql);
                    callableStatement.setString("accountNumber", accountNumber);
                    callableStatement.setDouble("money", temp);
                    callableStatement.setDouble("balance", account.getBalance() + temp);
                    callableStatement.executeQuery();
                } catch (SQLException e) {
                } finally {
                    try {
                        ConnectDB.closeConnect(connection);
                        if (callableStatement != null) {
                            callableStatement.close();
                        }
                    } catch (SQLException e) {
                    }
                }
                reponse = new ResultReponse();
                reponse.setSuccess(true);
                reponse.setNote("Hưởng lãi xuất thành công");
            } else {
                reponse.setSuccess(false);
                reponse.setNote("Không thể hưởng lãi xuất, quá hạn hoặc chưa đến hạn hưởng lãi xuất");
            }
        } else {
            reponse.setSuccess(false);
            reponse.setNote("Không thể hưởng lãi xuất, quá hạn hoặc chưa đến hạn hưởng lãi xuất");
        }
        return reponse;
    }
}
